package sg.edu.nus.dashboard.service.advisor;

import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import sg.edu.nus.dashboard.service.sqlexecutor.SQLService;
import sg.edu.nus.dashboard.vo.AdviceVo;
import sg.edu.nus.dashboard.vo.BaselineVo;

public class AdvisorService {

	public List<AdviceVo> getAdviceByFlag(boolean flag) {
		try {
			List<AdviceVo> list = new ArrayList<AdviceVo>();
			String sql = this.contructSelectAdviceSQL(flag);
			System.out.println(sql);
			SQLService s = new SQLService();
			List result = s.execute(sql);
			for (int i = 1; i < result.size(); i++) {
				Vector v = (Vector) result.get(i);
				AdviceVo adviceVo = this.convertVectorToVo(v);
				list.add(adviceVo);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public boolean updateAdviceById(int adviceId) {
		try {
			String sql = this.constructUpdateAdviceSQL(adviceId);
			System.out.println(sql);
			SQLService s = new SQLService();
			boolean result = s.executeUpdate(sql);

			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean updateAdviceByGroup(List<AdviceVo> list) {

		try {
			String sql = this.constructGroupUpdateAdviceSQL(list);
			System.out.println(sql);
			SQLService s = new SQLService();
			boolean result = s.executeUpdate(sql);

			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	private String contructSelectAdviceSQL(boolean flag) {
		String sql = "select * from OPD_ADVICE where FLAG='";
		if (flag) {
			sql += "1'";
		} else {
			sql += "0'";
		}
		sql += " order by ad_id";
		return sql;
	}

	private AdviceVo convertVectorToVo(Vector v) {
		int adviceId = ((java.math.BigDecimal) v.get(0)).intValue();
		String info = (String) v.get(1);
		boolean flag = (((java.math.BigDecimal) v.get(3)).intValue() == 1);

		AdviceVo vo = new AdviceVo();
		vo.setAdviceId(adviceId);
		vo.setInfo(info);
		vo.setFlag(flag);
		return vo;
	}

	private String constructUpdateAdviceSQL(int adviceId) {
		String sql = "update OPD_ADVICE set FLAG='0' ";
		sql += "where AD_ID='" + adviceId + "'";
		return sql;
	}

	private String constructGroupUpdateAdviceSQL(List<AdviceVo> list) {
		String sql = null;
		if (list.size() > 0) {
			sql = "update OPD_ADVICE set FLAG='1' ";
			sql += "where AD_ID in ('";
			for (int i = 0; i < list.size() - 1; i++) {
				sql += list.get(i).getAdviceId() + "','";
			}
			sql += list.get(list.size() - 1).getAdviceId() + "')";
		}
		return sql;
	}

	public static void main(String[] args) {
		AdvisorService c = new AdvisorService();
		List<AdviceVo> resultList = c.getAdviceByFlag(false);
		for (AdviceVo o : resultList)
			System.out.println(o.getAdviceId() + " : " + o.getInfo() + " : "
					+ o.isFlag());

		boolean result = c.updateAdviceById(1);
		System.out.println("result: " + result);

		c.updateAdviceByGroup(resultList);

	}
}
